package com.example.wechatdaka.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.wechatdaka.entity.UserStudent;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author lx
 * @since 2020-11-26
 */
public interface UserStudentMapper extends BaseMapper<UserStudent> {
    @Select("select id as lxUserId,lxUserNumber,lxUsername,lxUserSex,lxUserPhone from lx_user where id not in(select lxUserId from  lx_user_student) and lxRoleId=3 order by lxUserNumber")
    public List<UserStudent> selectStudentList();
    @Select("select s.id,u.id as lxUserId,u.lxUserNumber,u.lxUsername,u.lxUserSex,c.lxClazzName,u.lxUserPhone,u.lxUserEmail\n" +
            "from lx_user_student s\n" +
            "left join lx_clazz c on s.lxClazzId=c.id\n" +
            "left join lx_user u on s.lxUserId=u.id\n" +
            "ORDER BY c.id desc, u.lxUserNumber")
    public List<UserStudent> queryAllStudent();
    @Select("select s.id,u.id as lxUserId,u.lxUserNumber,u.lxUsername,u.lxUserSex,c.lxClazzName,u.lxUserPhone,u.lxUserEmail\n" +
            "from lx_user_student s\n" +
            "left join lx_clazz c on s.lxClazzId=c.id\n" +
            "left join lx_user u on s.lxUserId=u.id\n" +
            "ORDER BY c.id desc, u.lxUserNumber")
    public IPage<UserStudent> queryAllStudentByPage(Page page);
    @Select("select s.id,u.id as lxUserId,u.lxUserNumber,u.lxUsername,u.lxUserSex,c.lxClazzName,u.lxUserPhone,u.lxUserEmail\n" +
            "from lx_user_student s\n" +
            "left join lx_clazz c on s.lxClazzId=c.id\n" +
            "left join lx_user u on s.lxUserId=u.id\n" +
            "where c.id=#{id}\n" +
            "ORDER BY c.id desc, u.lxUserNumber")
    public IPage<UserStudent> queryClazzStudentByPage(Page page,String id);
    @Select("select s.id,u.id as lxUserId,u.lxUserNumber,u.lxUsername,u.lxUserSex,c.lxClazzName,u.lxUserPhone,u.lxUserEmail\n" +
            "from lx_user_student s\n" +
            "left join lx_clazz c on s.lxClazzId=c.id\n" +
            "left join lx_user u on s.lxUserId=u.id\n" +
            "where u.lxUserNumber=#{lxUserNumber}")
    public UserStudent queryByUserNumber(String lxUserNumber);
}
